using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using Pheonix.Fashionzoomer.BLL;
using Pheonix.Fashionzoomer.Entity.SqlServer2000;
using Pheonix.CodeLibrary.Data;
using Pheonix.CodeLibrary.Web;
using System.Collections.Generic;
using Pheonix.CodeLibrary;

/// <summary>
/// Summary description for CustomCommand
/// </summary>

namespace Pheonix.Fashionzoomer.DAL
{
    public sealed class CustomCommand : SqlServerDAO
    {
        private CustomCommand() {}

        public static CurrentUser GetBasicUserInfo(int userId)
        {
            if (userId <= 0)
                throw (new ArgumentNullException("userId in CustomCommand.GetBasicUserInfo()"));

            try
            {
                CurrentUser currentUser = new CurrentUser();

                SqlCommand sqlCmd = new SqlCommand();
                AddParameterToSqlCmd(sqlCmd, "@Id", SqlDbType.Int, 0, ParameterDirection.Input, userId);
                SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GETUSERBASICINFO);
                DataRow row = GetRowData(sqlCmd);

                if (row.ItemArray.Length > 0 && row["user_name"] != null)
                {
                    currentUser.Id = userId;
                    currentUser.UserName = row["user_name"].ToString();
                    currentUser.UserEmail = row["user_email"].ToString();
                    currentUser.FullName= row["user_first_name"].ToString() + " " + row["user_last_name"].ToString();
                    currentUser.LastVisit = DateTime.Parse(row["last_visit"].ToString());
                    currentUser.MembershipId = Int32.Parse(row["membership_id"].ToString());
                }

                return currentUser; 
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static bool IsUserNameAlreadyRegistered(string userName)
        {
            if (String.IsNullOrEmpty(userName))
                throw (new ArgumentNullException("userName in CustomCommand.IsEmailAlreadyRegistered()"));

            try
            {
                SqlCommand sqlCmd = new SqlCommand();
                AddParameterToSqlCmd(sqlCmd, "@UserName", SqlDbType.NVarChar, 50, ParameterDirection.Input, userName);
                SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GETUSERNAMECOUNT);
                int result = (int)GetOneData(sqlCmd);

                if (result > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static bool VerifyConfirmation(string confirmationCode, out CurrentUser currentUser)
        {
            if( String.IsNullOrEmpty(confirmationCode))
                throw (new ArgumentNullException("confirmationCode in CustomCommand.VerifyConfirmation()"));

            currentUser = new CurrentUser();

            try
            {
                SqlCommand sqlCmd = new SqlCommand();
               
                AddParameterToSqlCmd(sqlCmd, "@ConfirmationCode", SqlDbType.NVarChar, 50, ParameterDirection.Input, confirmationCode);
                SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GET_USER_ID_BY_CODE);
                int? result = (int?)GetOneData(sqlCmd);

                if (result.HasValue)
                {
                    SqlCommand updateCmd = new SqlCommand();
                    AddParameterToSqlCmd(updateCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
                    AddParameterToSqlCmd(updateCmd, "@Id", SqlDbType.Int, 0, ParameterDirection.Input, result.Value);
                    SetCommandType(updateCmd, CommandType.StoredProcedure, SPR_CUSTOM_VERIFYUSERACCOUNT);
                    GetOneData(updateCmd);
                    int? updateResult = (int?)updateCmd.Parameters["@ReturnValue"].Value;

                    if (updateResult.HasValue && updateResult.Value > 0)
                    {
                        currentUser = GetBasicUserInfo(result.Value);
                        return true;
                    }
                    else
                        return false;
                }
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetProductSearchResult(decimal minPriceValue, decimal maxPriceValue, bool maleFemaleValue,
            string keywordsValue)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@MinPriceValue", SqlDbType.Decimal, 0, ParameterDirection.Input, minPriceValue);
            AddParameterToSqlCmd(sqlCmd, "@MaxPriceValue", SqlDbType.Decimal, 0, ParameterDirection.Input, maxPriceValue);
            AddParameterToSqlCmd(sqlCmd, "@MaleFemaleValue", SqlDbType.Bit, 0, ParameterDirection.Input, maleFemaleValue);
            AddParameterToSqlCmd(sqlCmd, "@KeywordsValue", SqlDbType.NVarChar, 512, ParameterDirection.Input, keywordsValue);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_SEARCHPRODUCT);

            return GetTableData(sqlCmd);
        }

        public static DataTable GetProductByShopSearchResult(decimal minPriceValue, decimal maxPriceValue,
            int brandValue, bool maleFemaleValue, string keywordsValue)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@MinPriceValue", SqlDbType.Decimal, 0, ParameterDirection.Input, minPriceValue);
            AddParameterToSqlCmd(sqlCmd, "@MaxPriceValue", SqlDbType.Decimal, 0, ParameterDirection.Input, maxPriceValue);
            AddParameterToSqlCmd(sqlCmd, "@MaleFemaleValue", SqlDbType.Bit, 0, ParameterDirection.Input, maleFemaleValue);
            AddParameterToSqlCmd(sqlCmd, "@KeywordsValue", SqlDbType.NVarChar, 512, ParameterDirection.Input, keywordsValue);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_SEARCHPRODUCT);

            return GetTableData(sqlCmd);
        }

        public static DataTable GetProductByCategorySearchResult(decimal minPriceValue, decimal maxPriceValue,
            int categoryValue, bool maleFemaleValue, string keywordsValue)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@MinPriceValue", SqlDbType.Decimal, 0, ParameterDirection.Input, minPriceValue);
            AddParameterToSqlCmd(sqlCmd, "@MaxPriceValue", SqlDbType.Decimal, 0, ParameterDirection.Input, maxPriceValue);
            AddParameterToSqlCmd(sqlCmd, "@MaleFemaleValue", SqlDbType.Bit, 0, ParameterDirection.Input, maleFemaleValue);
            AddParameterToSqlCmd(sqlCmd, "@KeywordsValue", SqlDbType.NVarChar, 512, ParameterDirection.Input, keywordsValue);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_SEARCHPRODUCT);

            return GetTableData(sqlCmd);
        }

        /// <summary>
        /// Gets the list of data to populate Product Category dropdownlist
        /// </summary>
        /// <returns>A datatable with category_name and id columns for category list drop down to bind</returns>
        public static DataTable GetCategoryForDropdown()
        {
            SqlCommand sqlCmd = new SqlCommand();

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GETCATEGORYLIST);

            return GetTableData(sqlCmd);
        }

        /// <summary>
        /// Gets the list of data to populate Product Shop dropdownlist
        /// </summary>
        /// <returns>A datatable with shop_name and id columns for shop list drop down to bind</returns>
        public static DataTable GetShopForDropdown()
        {
            SqlCommand sqlCmd = new SqlCommand();

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GETSHOPLIST);

            return GetTableData(sqlCmd);
        }

        public static bool DeleteSubscribeByEmail(string email)
        {
            //Validate Input
            if (String.IsNullOrEmpty(email))
                throw (new ArgumentOutOfRangeException("email in DeleteSubscribeByEmail"));

            // Execute SQL Command
            try
            {
                SqlCommand sqlCmd = new SqlCommand();
                AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
                AddParameterToSqlCmd(sqlCmd, "@Email", SqlDbType.NVarChar, 64, ParameterDirection.Input, email);
                SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_DELETESUBSCRIBEBYEMAIL);
                GetOneData(sqlCmd);
                int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
                return (returnValue == 0 ? false : true);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static bool IsAlreadySubscribedForNewsletter(string email)
        {
            //Validate Input
            if (String.IsNullOrEmpty(email))
                throw (new ArgumentOutOfRangeException("email in IsAlreadySubscribedForNewsletter"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@Email", SqlDbType.NVarChar, 64, ParameterDirection.Input, email);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_ISALREADYREGISTEREDFORNEWSLETTER);
            GetOneData(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);
        }

        /// <summary>
        /// This method matches the provided username and passwrod in the tbl_user
        /// to verify authentication of a registered user information. Basically
        /// it purpose is to login a user.
        /// </summary>
        /// <param name="userName">username to be searched for</param>
        /// <param name="password">password to be searched for</param>
        /// <returns>returns an User entity instance</returns>
        public static DataRow GetUserByUsernameAndPassword(string userName, string password)
        {
            //Validate Input
            if (String.IsNullOrEmpty(userName))
                throw (new ArgumentOutOfRangeException("userName in GetUserByUsernameAndPassword"));
            if (String.IsNullOrEmpty(password))
                throw (new ArgumentOutOfRangeException("password in GetUserByUsernameAndPassword"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@Username", SqlDbType.NVarChar, 32, ParameterDirection.Input, userName);
            AddParameterToSqlCmd(sqlCmd, "@Password", SqlDbType.NVarChar, 32, ParameterDirection.Input, password);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GETUSERBYUSERNAMEANDPASSWORD);

            return GetRowData(sqlCmd);
        }

        public static DataTable GetMessageByReceiverUserId(int ReceiverUserId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReceiverUserId", SqlDbType.Int, 4, ParameterDirection.Input, ReceiverUserId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_MESSAGEBYRECEIVERUSERID);

            return GetTableData(sqlCmd);
        }

        public static DataTable GetAllMessage()
        {
            SqlCommand sqlCmd = new SqlCommand();
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GETALLMESSAGE);
            return GetTableData(sqlCmd);
        }

        
        public static DataTable GetMessageBySearchKey(string searchKey)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@SearchKey", SqlDbType.VarChar, 100, ParameterDirection.Input, searchKey);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GETMESSAGEBYSEARCHKEY);
            return GetTableData(sqlCmd);
        }

        public static DataTable GetShopListBySearch(string brand, string shopId, int category, string keywords)
        {
            SqlCommand sqlCmd = new SqlCommand();
            if (brand == String.Empty)
                brand = "%";
            if (shopId == String.Empty)
                shopId = "%";
            if (keywords == String.Empty)
                keywords = "%";
            
            AddParameterToSqlCmd(sqlCmd, "@Brand", SqlDbType.VarChar, 100, ParameterDirection.Input, brand);
            AddParameterToSqlCmd(sqlCmd, "@ShopId", SqlDbType.VarChar, 100, ParameterDirection.Input, shopId);
            AddParameterToSqlCmd(sqlCmd, "@Category", SqlDbType.Int, 0, ParameterDirection.Input, category);
            AddParameterToSqlCmd(sqlCmd, "@Keywords", SqlDbType.VarChar, 500, ParameterDirection.Input, keywords);
            
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GETSHOPLISTBYSEARCH);
            return GetTableData(sqlCmd);
        }

        public static DataTable GetShopCategoryByShopId(int shopId)
        {
            if (shopId > 0)
            {
                SqlCommand sqlCmd = new SqlCommand();
                AddParameterToSqlCmd(sqlCmd, "@ShopId", SqlDbType.Int, 0, ParameterDirection.Input, shopId);

                SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GETSHOPCATEGORYBYSHOPID);
                return GetTableData(sqlCmd);
            }
            else
                return new DataTable();
        }

        public static DataTable GetRelatedProductTo(int currentProductId, int categoryId)
        {
            if (currentProductId < 0)
                throw (new ArgumentNullException("currentProductId in CustomCommand.GetRelatedProductTo()"));

            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CurrentProductId", SqlDbType.Int, 0, ParameterDirection.Input, currentProductId);
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GETRELATEDPRODUCT);
            return GetTableData(sqlCmd);
        }

        public static DataTable GetFeaturedProductList()
        {
            SqlCommand sqlCmd = new SqlCommand();
            
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GETFEATUREDPRODUCT);
            return GetTableData(sqlCmd);
        }

        public static DataTable GetFeaturedShopList()
        {
            SqlCommand sqlCmd = new SqlCommand();

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GETFEATUREDSHOP);
            return GetTableData(sqlCmd);
        }

        public static DataTable GetShopDetail(int shopId)
        {
            if (shopId < 0)
                throw (new ArgumentNullException("shopId in CustomCommand.GetShopDetail())"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@Id", SqlDbType.Int, 0, ParameterDirection.Input, shopId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GETSHOPDETAILS);
            return GetTableData(sqlCmd);
        }

        public static DataTable GetShopProducts(int shopId)
        {
            if (shopId < 0)
                throw (new ArgumentNullException("shopId in CustomCommand.GetShopProducts())"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ShopId", SqlDbType.Int, 0, ParameterDirection.Input, shopId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GETSHOPPRODUCT);
            return GetTableData(sqlCmd);
        }

        public static bool IsShopNameFree(string shopName)
        {
            if (string.IsNullOrEmpty(shopName))
                throw (new ArgumentNullException("shopName in CustomCommand.IsShopNameFree())"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ShopName", SqlDbType.NVarChar, 250, ParameterDirection.Input, shopName);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GET_SHOP_COUNT_BY_SHOP_NAME);
            return (Convert.ToInt32(GetOneData(sqlCmd))>0)?false:true;
        }

        /// <summary>
        /// This method matches the provided username in the tbl_user
        /// to return user information.
        /// </summary>
        /// <param name="userName">username to be searched for</param>
        /// <returns>returns an User entity instance</returns>
        public static DataRow GetUserByUsername(string userName)
        {
            //Validate Input
            if (String.IsNullOrEmpty(userName))
                throw (new ArgumentOutOfRangeException("userName in GetUserByUsername"));
            
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@Username", SqlDbType.NVarChar, 32, ParameterDirection.Input, userName);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GETUSERBYUSERNAME);

            return GetRowData(sqlCmd);
        }

        public static bool IsPackageNameFree(string packageName)
        {
            if (string.IsNullOrEmpty(packageName))
                throw (new ArgumentNullException("packageName in CustomCommand.IsPackageNameFree())"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@PackageName", SqlDbType.NVarChar, 250, ParameterDirection.Input, packageName);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GET_MEMBERSHIP_COUNT_BY_PACKAGE_NAME);
            return (Convert.ToInt32(GetOneData(sqlCmd)) > 0) ? false : true;
        }

        /// <summary>
        /// Checks whether a category name already exists or not, used
        /// in category creation or modification
        /// </summary>
        /// <param name="categoryName"></param>
        /// <returns></returns>
        public static bool IsCategoryNameFree(string categoryName)
        {
            if (string.IsNullOrEmpty(categoryName))
                throw (new ArgumentNullException("categoryName in CustomCommand.IsCategoryNameFree())"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 250, ParameterDirection.Input, categoryName);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GET_CATEGORY_COUNT_BY_CATEGORY_NAME);
            return (Convert.ToInt32(GetOneData(sqlCmd)) > 0) ? false : true;
        }

        public static CustomCollection<Category> GetCategoriesById(List<int> idToDelete)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            string idsCSV = Utility.MakeCSV(idToDelete);
            AddParameterToSqlCmd(sqlCmd, "@CategoryIds", SqlDbType.NVarChar, 250, ParameterDirection.Input, idsCSV);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GET_CATEGORY_BY_IDS);
            GenerateCollectionFromReader result = new GenerateCollectionFromReader(Category.GenerateCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)GetAllData(sqlCmd, result));

            return objCollection;
        }

        public static CustomCollection<Product> GetAllProductsWithProductId(List<int> idToDelete)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            string idsCSV = Utility.MakeCSV(idToDelete);
            AddParameterToSqlCmd(sqlCmd, "@ProductIds", SqlDbType.NVarChar, 250, ParameterDirection.Input, idsCSV);
            
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GET_ALL_PRODUCT_BY_PRODUCT_IDS);
            GenerateCollectionFromReader result = new GenerateCollectionFromReader(Product.GenerateProductCollectionFromReader);
            CustomCollection<Product> objCollection = ((CustomCollection<Product>)GetAllData(sqlCmd, result));

            return objCollection;
        }

        public static CustomCollection<Shop> GetShopsById(List<int> idToDelete)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            string idsCSV = Utility.MakeCSV(idToDelete);
            AddParameterToSqlCmd(sqlCmd, "@ShopIds", SqlDbType.NVarChar, 250, ParameterDirection.Input, idsCSV);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GET_SHOP_BY_IDS);
            GenerateCollectionFromReader result = new GenerateCollectionFromReader(Shop.GenerateShopCollectionFromReader);
            CustomCollection<Shop> objCollection = ((CustomCollection<Shop>)GetAllData(sqlCmd, result));

            return objCollection;
        }

        public static void IncreaseCategoryItemCount(int categoryId, int shopId)
        {
            if (categoryId <= 0)
                throw (new ArgumentNullException("categoryId in CustomCommand.IncreaseCategoryItemCount())"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@ShopId", SqlDbType.Int, 0, ParameterDirection.Input, shopId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_INCREASE_CATEGORY_ITEM_COUNT);

            GetOneData(sqlCmd);
        }

        public static void DecreaseCategoryItemCount(int categoryId, int shopId)
        {
            if (categoryId <= 0)
                throw (new ArgumentNullException("categoryId in CustomCommand.IncreaseCategoryItemCount())"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@ShopId", SqlDbType.Int, 0, ParameterDirection.Input, shopId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_DECREASE_CATEGORY_ITEM_COUNT);

            GetOneData(sqlCmd);
        }

        public static bool IsAlreadyAddedInFavorite(int userId, int productId)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@UserId", SqlDbType.Int, 0, ParameterDirection.Input, userId);
            AddParameterToSqlCmd(sqlCmd, "@ProductId", SqlDbType.Int, 0, ParameterDirection.Input, productId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_IS_ALREADY_ADDED_TO_FAVORITE);
            GetOneData(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);
        }

        public static DataTable GetFavoriteProducts(int userId)
        {
            if (userId < 0)
                throw (new ArgumentNullException("userId in CustomCommand.GetFavoriteProducts())"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@UserId", SqlDbType.Int, 0, ParameterDirection.Input, userId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GET_FAVORITE_PRODUCTS);
            return GetTableData(sqlCmd);
        }

        public static DataTable GetProductsByCategoryId(int categoryId)
        {
            if (categoryId < 0)
                throw (new ArgumentNullException("categoryId in CustomCommand.GetProductsByCategoryId())"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GET_PRODUCTS_BY_CATEGORY_ID);
            return GetTableData(sqlCmd);
        }

        public static bool CheckOldPassword(int userId, string userOldPassword)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@UserId", SqlDbType.Int, 0, ParameterDirection.Input, userId);
            AddParameterToSqlCmd(sqlCmd, "@UserOldPassword", SqlDbType.NVarChar, 250, ParameterDirection.Input, userOldPassword);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_CHECK_OLD_PASSWORD);
            GetOneData(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);
        }

        public static DataTable GetProductsByCategoryIdAndShopId(int categoryId, int shopId)
        {
            if (categoryId < 0)
                throw (new ArgumentNullException("categoryId in CustomCommand.GetProductsByCategoryIdAndShopId())"));
            if (shopId < 0)
                throw (new ArgumentNullException("shopId in CustomCommand.GetProductsByCategoryIdAndShopId())"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@ShopId", SqlDbType.Int, 0, ParameterDirection.Input, shopId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GET_PRODUCTS_BY_CATEGORY_ID_AND_SHOP_ID);
            return GetTableData(sqlCmd);
        }

        public static DataTable GetActiveProducts()
        {
            SqlCommand sqlCmd = new SqlCommand();

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_CUSTOM_GET_ALL_PRODUCTS);
            return GetTableData(sqlCmd);
        }

        #region constants

        private const string SPR_CUSTOM_GETUSERNAMECOUNT = "spr_Custom_GetUserNameCount";
        private const string SPR_CUSTOM_GET_USER_ID_BY_CODE = "spr_Custom_GetUserIdByCode";
        private const string SPR_CUSTOM_VERIFYUSERACCOUNT = "spr_Custom_VerifyUserAccount";
        private const string SPR_CUSTOM_GETUSERBASICINFO = "spr_Custom_GetUserBasicInfo";
        private const string SPR_CUSTOM_SEARCHPRODUCT = "spr_Custom_SearchProduct";
        private const string SPR_CUSTOM_GETCATEGORYLIST = "spr_Custom_GetCategoryList";
        private const string SPR_CUSTOM_GETSHOPLIST = "spr_Custom_GetShopList";
        private const string SPR_CUSTOM_DELETESUBSCRIBEBYEMAIL = "spr_Custom_DeleteSubscribeByEmail";
        private const string SPR_CUSTOM_ISALREADYREGISTEREDFORNEWSLETTER = "spr_Custom_IsAlreadyRegisteredForNewsletter";
        private const string SPR_CUSTOM_GETUSERBYUSERNAMEANDPASSWORD = "spr_Custom_GetUserByUsernameAndPassword";
        private const string SPR_CUSTOM_GETUSERBYUSERNAME = "spr_Custom_GetUserByUsername";
        private const string SPR_CUSTOM_MESSAGEBYRECEIVERUSERID = "spr_Custom_GetMessageByReceiverUserId";
        private const string SPR_CUSTOM_GETALLMESSAGE = "spr_Custom_GetAllMessage";
        private const string SPR_CUSTOM_GETMESSAGEBYSEARCHKEY = "spr_Custom_GetMessageBySearchKey";
        private const string SPR_CUSTOM_GETSHOPLISTBYSEARCH = "spr_Custom_GetShopListBySearch";
        private const string SPR_CUSTOM_GETSHOPCATEGORYBYSHOPID = "spr_Custom_GetShopCategoryByShopId";
        private const string SPR_CUSTOM_GETRELATEDPRODUCT = "spr_Custom_GetRelatedProduct";
        private const string SPR_CUSTOM_GETFEATUREDPRODUCT = "spr_Custom_GetFeaturedProduct";
        private const string SPR_CUSTOM_GETFEATUREDSHOP = "spr_Custom_GetFeaturedShop";
        private const string SPR_CUSTOM_GETSHOPDETAILS = "spr_Custom_GetShopDetails";
        private const string SPR_CUSTOM_GETSHOPPRODUCT = "spr_Custom_GetShopProducts";
        private const string SPR_CUSTOM_GET_SHOP_COUNT_BY_SHOP_NAME = "spr_Custom_Get_Shop_Count_By_Shop_Name";
        private const string SPR_CUSTOM_GET_MEMBERSHIP_COUNT_BY_PACKAGE_NAME = "spr_Custom_Get_Membership_Count_By_Package_Name";
        private const string SPR_CUSTOM_GET_CATEGORY_COUNT_BY_CATEGORY_NAME = "spr_Custom_Get_Category_Count_By_Category_Name";
        private const string SPR_CUSTOM_GET_CATEGORY_BY_IDS = "spr_Custom_Get_Category_By_Ids";
        private const string SPR_CUSTOM_GET_ALL_PRODUCT_BY_PRODUCT_IDS = "spr_Custom_Get_All_Product_By_Product_Ids";
        private const string SPR_CUSTOM_GET_SHOP_BY_IDS = "spr_Custom_Get_Shop_By_Ids";
        private const string SPR_CUSTOM_INCREASE_CATEGORY_ITEM_COUNT = "spr_Custom_IncreaseCategoryItemCount";
        private const string SPR_CUSTOM_DECREASE_CATEGORY_ITEM_COUNT = "spr_Custom_DecreaseCategoryItemCount";
        private const string SPR_CUSTOM_IS_ALREADY_ADDED_TO_FAVORITE = "spr_Custom_Is_Already_Added_To_Favorite";
        private const string SPR_CUSTOM_GET_FAVORITE_PRODUCTS = "spr_Custom_Get_Favorite_Products";
        private const string SPR_CUSTOM_GET_PRODUCTS_BY_CATEGORY_ID = "spr_Custom_Get_Products_By_Category_Id";
        private const string SPR_CUSTOM_CHECK_OLD_PASSWORD = "spr_Custom_Check_Old_Password";
        private const string SPR_CUSTOM_GET_PRODUCTS_BY_CATEGORY_ID_AND_SHOP_ID = "spr_Custom_Get_Products_By_Category_Id_And_Shop_Id";
        private const string SPR_CUSTOM_GET_ALL_PRODUCTS = "spr_Custom_Get_All_Products";
        #endregion    
    }
}
